const mysql = require('mysql2/promise'),
      { con, ResData } = require('./common');

/*根据类型查询计划*/
async function getPlans(cla_id){
  let res = new ResData();
  let sql = `select plan_id, plan_name, plan_img, tag_name, classification,plan_week, plan_rec
    from plans, tags
    where plan_tag = tag_id and classification=${cla_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*查询所有计划*/
async function getAllPlans(cla_id){
  let res = new ResData();
  let sql = `select plan_id, plan_name, plan_img, tag_name, cla_name,plan_week, plan_rec
        from plans, tags, classification
            where plan_tag = tag_id and classification=cla_id;`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*计划详情*/
async function getAPlan(plan_id){
  let res = new ResData();
  let sql = `select plan_id, plan_name, plan_img, tag_name, cla_name, plan_week, plan_rec
        from plans, tags, classification
            where plan_tag = tag_id and classification=cla_id and plan_id=${plan_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*定制计划*/
async function makePlan(user_id, height, weight, reducedfat, musclebuild, weatherrun, daysaweek, minsaday){
  let res = new ResData();
  let sql = `insert into healthdata(user_id,height,weight) 
        values('${user_id}','${height}','${weight}') 
    on duplicate key update height= '${height}',weight='${weight}';`;
  let sql2 = ` insert into makeplan(user_id, reducedfat, musclebuild, weatherrun, daysaweek, minsaday)
        values('${user_id}', '${reducedfat}', '${musclebuild}', '${weatherrun}', '${daysaweek}', '${minsaday}')
    on duplicate key update reducedfat='${reducedfat}', musclebuild='${musclebuild}',weatherrun='${weatherrun}',daysaweek='${daysaweek}',minsaday='${minsaday}';`;
  try{
    var [rows] = await con.execute(sql);
    var [rows] = await con.execute(sql2);
  }catch(e){
    res.code = 401002;
    res.msg = 'insert failed';
  }
  return res;
}
/*查询计划详情*/
async function getChildplanByPlanid(plan_id){
  let res = new ResData();
  let sql = `select plan_id, cp_id, cp_name, cp_time, cp_src, cp_week, cp_day 
    from childplans
    where plan_id = ${plan_id};`;
  console.log(plan_id);
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*用户添加计划*/
async function addUserPlan(user_id, plan_id){
  let res = new ResData();
  let sql = `insert into userplans(user_id, plan_id) values ('${user_id}', '${plan_id}');`;
  try{
    var [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401002;
    res.msg = 'insert failed';
  }
  return res;
};
/*取消取消*/
async function delUserPlan(user_id, plan_id){
  let res = new ResData();
  let sql = `delete from userplans
    where user_id=${user_id} and plan_id = ${plan_id};`;
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401002;
    res.msg = 'delete failed';
  }
  return res;
};
/*查询用户所有计划*/
async function getUserPlans(user_id){
  let res = new ResData();
  let sql = `select plans.plan_id, plan_name, plan_img, tag_name, cla_name
from plans, tags, classification, userplans
where plan_tag = tag_id and classification=cla_id and userplans.plan_id = plans.plan_id and userplans.user_id=${user_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*删除计划*/
async function delPlan(plan_id){
  let res = new ResData();
  let sql = `delete from plans where plan_id=${plan_id};`;
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401002;
    res.msg = 'delete failed';
  }
  return res;
};
/*上传计划*/
async function addPlan(plan_id, plan_name, plan_img, plan_tag, classification, plan_week, plan_rec){
  let res = new ResData();
  let sql = `insert into plans(plan_id, plan_name, plan_img, plan_tag, classification, plan_week, plan_rec)
values(${plan_id}, '${plan_name}', '${plan_img}', '${plan_tag}', '${classification}', '${plan_week}', '${plan_rec}');`;
  console.log(sql);
  try{
    [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401002;
    res.msg = 'insert failed';
  }
  return res;
}
/*上传子计划*/
async function addChildPlan(plan_id, cp_id, cp_name, cp_time, cp_week, cp_day){
  let res = new ResData();
  let sql = `insert into childplans(plan_id, cp_id, cp_name, cp_time, cp_week, cp_day)
values (${plan_id}, ${cp_id}, '${cp_name}', '${cp_time}', ${cp_week}, ${cp_day});`;
  console.log(sql);
  try{
    [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401002;
    res.msg = 'insert failed';
  }
  return res;
}
/*删除子计划*/
async function delChildPlan(plan_id, cp_id){
  let res = new ResData();
  let sql = `delete from childplans where plan_id=${plan_id} and cp_id=${cp_id};`;
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401002;
    res.msg = 'delete failed';
  }
  return res;
};


module.exports = {
  getPlans,
  makePlan,
  getChildplanByPlanid,
  getAPlan,
  getAllPlans,
  addUserPlan,
  delUserPlan,
  getUserPlans,
  delPlan,
  addPlan,
  addChildPlan,
  delChildPlan
};
